package com.cola.dao;

import com.cola.entity.export.ExportShop;
import com.cola.utils.DBControl;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 类名：ShopDao
 * 功能：店铺管理dao
 * 作者：Tony
 * 日期：2017/8/22.
 */
public class ShopDao extends BaseDao{
    private final Logger logger = LoggerFactory.getLogger(getClass());
    private static volatile ShopDao instance = null;

    public static ShopDao getInstance() {
        if (instance == null) {
            synchronized (ShopDao.class) {
                if (instance == null) {
                    instance = new ShopDao();
                }
            }
        }
        return instance;
    }

    /**
     * 功能：获取店铺列表
     * 参数：获取店铺列表的条件
     * 返回值：获取的店铺列表
     * 作者：tony
     */
    public List<Map<String, Object>> getShopList(Integer beginNum, Integer pageSize, String filter){
        Connection conn = null;
        try {
            conn = DBControl.getConnection();
            String sql = "select * from cola_customer where 1=1" + filter + "  limit ?,?";
            return getQueryRunner().query(conn,sql,new MapListHandler(),beginNum,pageSize);
        } catch (Exception e) {
            logger.error("获取店铺列表异常！",e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 根据地区名称获取编号
     * @param area_name 地区名称
     * @return area_no 地区编号
     * @author tony
     * @since 2017/9/7 14:32
     */
    public String getAreaNoByAreaName(String area_name){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String area_no = "";
        try {
            conn = DBControl.getConnection();
            String sql = "select area_no from system_area where area_name = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,area_name);
            rs = ps.executeQuery();
            while(rs.next()){
                area_no = rs.getString(1);
            }
        } catch (Exception e) {
            logger.error("根据地区编号获取地区名称失败！",e);
        } finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return area_no;
    }

    /**
     * 获取地区区域的名称
     * @param area_no
     * @return
     * @author tony
     * @since 2017/9/6 18:30
     */
    public String getAreaNameByAreaNo(String area_no){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String area_name="";
        try {
            conn = DBControl.getConnection();
            String sql = "select area_name from system_area where area_no = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,area_no);
            rs = ps.executeQuery();
            while (rs.next()){
                area_name = rs.getString(1);
            }
        } catch (Exception e) {
            logger.error("获取地区区域的名称异常！",e);
        } finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return area_name;
    }

    /**
     * 根据渠道类型编号获取渠道名称
     * @param channel_type_no 渠道类型编号
     * @return 渠道名称
     * @author tony
     * @since 2017/9/11 11:24
     */
    public String getChannelType(String channel_type_no){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String channel_type = "";
        try {
            conn = DBControl.getConnection();
            String sql = "select `name` from system_dictionary where type = 'CHANNEL' and `order` = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,channel_type_no);
            rs = ps.executeQuery();
            while(rs.next()){
                channel_type = rs.getString(1);
            }
        } catch (Exception e) {
            logger.error("根据渠道类型编号获取渠道名称异常！",e);
        } finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return channel_type;
    }

    /**
     * 功能：获取店铺数量
     * 参数：筛选条件
     * 返回：符合条件的店铺数量
     * 作者：tony
     */
    public Integer getShopCount(String filter){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select count(customer_id) from cola_customer where 1=1" + filter ;
        try {
            conn = DBControl.getConnection();
            ps=conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }
        } catch (Exception e) {
            logger.error("获取店铺数量异常！",e);
        } finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return 0;
    }

    /**
     * 功能：禁封店铺，解封店铺
     * 参数：店铺编号以及店铺在操作之后的状态
     * 返回：false为操作失败，true为操作成功
     * 作者：tony
     */
    public boolean updateCheckStatus(Integer customerId, Integer check_status){
        Connection conn=null;
        try {
            conn = DBControl.getConnection();
            String sql="update cola_customer set check_status=? where customer_id=?";
            return getQueryRunner().update(conn,sql,check_status,customerId)>0;
        } catch (Exception e) {
            logger.error("更改店铺审核状态异常！",e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 功能：管理店铺
     * 参数：被管理店铺的编号以及管理的信息
     * 返回：false为管理失败，true为管理成功
     * 作者：tony
     */
    public boolean updateManagerCustomer(Integer customerId, String customer_no, String office_address, String routeId, Integer check_status){
        Connection conn=null;
        try {
            conn = DBControl.getConnection();
            String sql="update cola_customer set customer_no = ?, office_address = ?, route_id = ?, check_status=? where customer_id = ?";

            if (check_status!=-2) {
                if(StringUtils.isEmpty(customer_no)||StringUtils.isEmpty(office_address)||StringUtils.isEmpty(routeId)){
                    return getQueryRunner().update(conn,sql,customer_no,office_address,routeId,1,customerId)>0;
                }else {
                    return getQueryRunner().update(conn, sql, customer_no, office_address, routeId, 2, customerId) > 0;
                }
            } else {
                return getQueryRunner().update(conn,sql,customer_no,office_address,routeId,-2,customerId)>0;
            }
        } catch (Exception e) {
            logger.error("管理店铺失败！",e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 功能：根据店铺编号获取店铺信息
     * 参数：店铺编号
     * 返回：对应的店铺信息
     * 作者：tony
     */
    public Map<String, Object> getCustomerInfoById(Integer customerId){
        Connection conn=null;
        try {
            conn = DBControl.getConnection();
            String sql= "select * from cola_customer where customer_id=?";
            return getQueryRunner().query(conn,sql,new MapHandler(),customerId);
        } catch (Exception e) {
            logger.error("根据customerId获取信息异常！",e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 根据店铺系统编号获取店铺指定经销商
     * @return
     * @author tony
     * @since 2017/9/8 17:35
     */
    public List<String> getMepNoByCustomerId(String customerSystemNo){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<String> list = new ArrayList<>();
        String sql = "select mep_no from cola_recommend_mep where customer_system_no = "+ customerSystemNo;
        try {
            conn = DBControl.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                String a = rs.getString(1);
                list.add(a);
            }
        } catch (Exception e) {
            logger.error("根据店铺系统编号获取店铺指定经销商异常！",e);
        } finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return list;
    }

    /**
     * 功能：导入店铺信息
     * 参数：一条店铺信息list
     * 返回：false表示导入失败,true表示导入成功
     * 作者：tony
     */
    public boolean importShopInfo(Connection conn,List<String> list) throws Exception{
        boolean flag;
        try {
            flag = insertInfo(conn,list);
        } catch (Exception e) {
            logger.error("导入店铺信息异常！",e);
            throw e;
        }
        if(flag){
            return true;
        }
        return false;
    }

    /**
     * 功能：检查推荐经销商表内是否系统编号一样的数据
     * 参数：系统编号
     * 返回：false代表没有数据冲突,true代表有冲突
     * 作者：tony
     */
    public boolean checkSystemNo(String customer_system_no){
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        int count = 0;
        String sql = "select count(*) from cola_recommend_mep where customer_system_no = ?";
        try {
            conn = DBControl.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,customer_system_no);
            rs = ps.executeQuery();
            while(rs.next()){
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            logger.error("检查表内是否有数据冲突异常！",e);
        } finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return count>0;
    }

    /**
     * 检查店铺表内是否有系统编号为xx的数据
     * @param conn 数据库连接
     * @param customer_system_no 系统编号
     * @return true代表有，false代表无
     * @author tony
     * @since 2017/9/11 15:01
     */
    public boolean checkCustomerSystemNo(Connection conn,String customer_system_no) throws Exception{
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        String sql="select count(*) from cola_customer where customer_system_no = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,customer_system_no);
            rs = ps.executeQuery();
            while(rs.next()){
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            logger.error("检查店铺表内是否有系统编号为xx的数据异常！",e);
            throw e;
        } finally {
            DbUtils.closeQuietly(null,ps,rs);
        }
        return count>0;
    }


    /**
     * 功能：向推荐经销商表插入信息
     * 参数：一条店铺导入信息
     * 返回：false代表插入失败，true代表插入成功
     * 作者：tony
     */
    public boolean insertInfo(Connection conn,List<String> list) throws Exception{
        PreparedStatement ps = null;
        Integer i = 0;
        String sql =  "insert into cola_recommend_mep( customer_system_no, mep_no, status, create_time) values(?,?,?,NOW())";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,list.get(0));
            ps.setString(2,list.get(13));
            ps.setString(3,list.get(9));
//            ps.addBatch();
            i = ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            logger.error("向推荐经销商表插入信息失败！",e);
            throw e;
        }
        return i>0;
    }

    /**
     * 功能：从推荐经销商表删除数据
     * 参数：店铺系统编号
     * 返回：false代表删除失败，true代表删除成功
     * 作者：tony
     */
    public boolean deleteInfo(Connection conn,String customer_system_no) throws Exception {
        PreparedStatement ps = null;
        int i = 0;
        String sql = "delete from cola_recommend_mep where customer_system_no = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,customer_system_no);
            i = ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            logger.error("从推荐经销商表删除数据失败！",e);
            throw e;
        }
        return i>0;
    }

    /**
     * 导入申美，办事处，路线号等到店铺表
     * @param conn
     * @param map
     * @return map 结果和报错的系统编号
     * @author tony
     * @since 2017/9/11 12:51
     */
    public Map<String,Object> updateCustomerInfo(Connection conn, Map<String,Object> map) throws Exception{
        String sql;
        Map<String,Object> map1 = new HashMap<>();
        try {
            if(checkCustomerSystemNo(conn,(String) map.get("customer_system_no"))){
                sql = "update cola_customer set customer_no=?,office_address=?,route_id=?,customer_sub_channel_no=?,consumer_sub_channel_no=? " +
                        " where customer_system_no = ?";
                getQueryRunner().update(conn,sql,map.get("customer_no"),map.get("office_address"),
                        map.get("route_id"),map.get("customer_sub_channel_no"),map.get("consumer_sub_channel_no"),map.get("customer_system_no"));
                map1.put("result","success");
                map1.put("error","");
                return map1;
            }else{
                map1.put("result","fail");
                map1.put("error",map.get("customer_system_no"));
                return map1;
            }
        } catch (Exception e) {
            logger.error("导入申美，办事处，路线号等到店铺表异常！",e);
            throw e;
        }
    }


    /**
     * 获取导出店铺列表
     * @return List<ExportShop> 导出店铺列表
     * @author tony
     * @since 2017/9/6 15:27
     */
    public List<ExportShop> getExportShopList(String filter){
        Connection conn = null;
        try {
            conn = DBControl.getConnection();
            String sql = "select a.customer_system_no,customer_mobile,customer_contact_name,customer_name,shop_size,channel_type_no,city,area,address," +
                    " check_status,customer_no,office_address,route_id,b.mep_no,customer_sub_channel_no,consumer_sub_channel_no from cola_customer a " +
                    " INNER JOIN cola_recommend_mep b on a.customer_system_no=b.customer_system_no "+ filter +"  INNER JOIN system_area c on c.area_no = a.city;";
            return getQueryRunner().query(conn, sql, new BeanListHandler<ExportShop>(ExportShop.class));
        } catch (Exception e) {
            logger.error("获取导出的店铺列表异常！",e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }



    public static void main(String[] args){
        getInstance().getMepNoByCustomerId("355739706983583744");
//        List<String> list = new ArrayList<String>();
//        list.add("354228749417451520");//系统编号
//        list.add("21321132143");
//        list.add("xiaoshu");
//        list.add("xz");
//        list.add("300㎡");
//        list.add("2321");
//        list.add("324");
//        list.add("32");
//        list.add("澳门路477号");
//        list.add("1");
//        list.add("111");
//        list.add("1");
//        list.add("2");
//        list.add("321243");
//        list.add("213");
//        list.add("3213");
//        System.out.println(list);
//        getInstance().importShopInfo(list);
    }

}
